import pymysql
import pandas as pd
from sqlalchemy import Column, String, Integer, DateTime, DECIMAL
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import or_, not_

"""
connection config & engine for sqlalchemy
"""
connect_info = 'mysql+pymysql://root:123456@192.168.56.11:3306/chuanqi_statistics?charset=utf8'
engine = create_engine(connect_info) #use sqlalchemy to build link-engine
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
# 创建对象的基类:
Base = declarative_base()

class ChargingPileInfo(Base):
    # 表的名字:
    __tablename__ = 'chargine_pile_info'
    # 表的结构:
    id = Column(Integer(), primary_key=True)
    charging_pile_name = Column(String(2000))
    charging_pile_addr = Column(String(2000))
    charging_pile_longitude = Column(DECIMAL(12, 6))
    charging_pile_latitude = Column(DECIMAL(12, 6))


class ParkingInfo(Base):
    # 表的名字:
    __tablename__ = 'v_parking_cluster'
    vin = Column(String(2000), primary_key=True)
    home_longitude = Column(DECIMAL(12, 6))
    home_latitude = Column(DECIMAL(12, 6))
    # company_longitude = Column(DECIMAL(12, 6))
    # company_latitude = Column(DECIMAL(12, 6))
    # weekend_longitude = Column(DECIMAL(12, 6))
    # weekend_latitude = Column(DECIMAL(12, 6))
    # other_longitude = Column(DECIMAL(12, 6))
    # other_latitude = Column(DECIMAL(12, 6))


class DBOperator:
    def get_charging_piles(self):
        # 创建session对象:
        session = DBSession()
        # 添加到session:
        result = session.query(ChargingPileInfo).all()
        return result

    def get_home_location(self):
        # 创建session对象:
        session = DBSession()
        # 添加到session:
        result = session.query(ParkingInfo).filter(ParkingInfo.home_longitude.isnot(None))
        return result

    def get_company_location(self):
        # 创建session对象:
        session = DBSession()
        # 添加到session:
        result = session.query(ParkingInfo).filter(ParkingInfo.company_longitude.isnot(None))
        return result

    def get_other_location(self):
        # 创建session对象:
        session = DBSession()
        # 添加到session:
        result = session.query(ParkingInfo).filter(ParkingInfo.other_longitude.isnot(None))
        return result


    def get_weekend_location(self):
        # 创建session对象:
        session = DBSession()
        # 添加到session:
        result = session.query(ParkingInfo).filter(ParkingInfo.weekend_longitude.isnot(None))
        return result

if __name__ == '__main__':
    db_session = DBOperator()
    res = db_session.get_charging_piles()
    for item in res:
        print(item.charging_pile_name + ".........." +
              str(item.charging_pile_longitude) + "...." + str(item.charging_pile_latitude))

    res = db_session.get_home_location()
    for item in res :
        print(item.vin + "...." + item.home_longitude + "," + item.home_latitude)
